package com.xxzb.pack0721.dao;

import com.xxzb.pack0721.bean.Emp;
import com.xxzb.pack0721.db.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//对emp表的CRUD操作
public class EmpDAO {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    //1.添加(1)
    public void add(String ename, String job, int mgr, double sal, double comm, int deptNo) {
        conn = DBManager.getConnection();
        String sql = "insert into gaofeng2207.emp values(null,?,?,?,now(),?,?,?,1)";
        try {
            ps = conn.prepareStatement(sql);
            //这里给？赋值
            ps.setString(1, ename);
            ps.setString(2, job);
            ps.setInt(3, mgr);
            ps.setDouble(4, sal);
            ps.setDouble(5, comm);
            ps.setInt(6, deptNo);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBManager.closeConn(conn, ps, null);
        }
    }

    //2.添加（2）
    public void add(Emp emp) {
        conn = DBManager.getConnection();
        String sql = "insert into gaofeng2207.emp values(null,?,?,?,now(),?,?,?,1)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, emp.getEname());
            ps.setString(2, emp.getJob());
            ps.setInt(3, emp.getMgr());
            ps.setDouble(4, emp.getSal());
            ps.setDouble(5, emp.getComm());
            ps.setInt(6, emp.getDeptNo());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.closeConn(conn, ps, null);
        }
    }

    //3.删除
    public void remove(int empNo) {
        conn = DBManager.getConnection();
        String sql = "update gaofeng2207.emp set empstate=0 where empno=? ";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, empNo);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBManager.closeConn(conn, ps, null);
        }
    }

    //4.添加或者修改在一起
    public void saveOrUpdate(Emp emp) {
        String sql;
        if (emp.getEmpNo() == null) {
            sql = "insert into gaofeng2207.emp values(null,?,?,?,now(),?,?,?,1)";
        } else {
            sql = "update gaofeng2207.emp set ename=?,job=?,mgr=?,sal=?,comm=?,deptno=? where empno=?";
        }
        conn = DBManager.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, emp.getEname());
            ps.setString(2, emp.getJob());
            ps.setInt(3, emp.getMgr());
            ps.setDouble(4, emp.getSal());
            ps.setDouble(5, emp.getComm());
            ps.setInt(6, emp.getDeptNo());
            if (emp.getEmpNo() != null) {
                ps.setInt(7, emp.getEmpNo());
            }
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBManager.closeConn(conn, ps, null);
        }
    }

    //5.全表查询
    public List<Emp> find() {
        //产生了一个集合
        List<Emp> list = new ArrayList<>();
        conn = DBManager.getConnection();
        String sql = "select * from gaofeng2207.emp where empstate=1";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                //将数据库返回的表转成集合
                int empNo=rs.getInt(1);
                String ename=rs.getString(2);
                String job=rs.getString(3);
                int mgr=rs.getInt(4);
                String hireDate=rs.getString(5);
                double sal=rs.getDouble(6);
                double comm=rs.getDouble(7);
                int deptNo=rs.getInt(8);
                //将查询处理的数据封装到一个对象中
                Emp emp=new Emp(empNo,ename,job,mgr,hireDate,sal,comm,deptNo,1);
                //将产生的对象放到集合中
                list.add(emp);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBManager.closeConn(conn,ps,rs);
        }
        return list;
    }

    //6.按id查询
    public Emp findByNo(int empNo){
        conn=DBManager.getConnection();
        String sql="select * from gaofeng2207.emp where empstate=1 and empno=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,empNo);
             rs = ps.executeQuery();
            if(rs.next()){
                int empNo1=rs.getInt(1);
                String ename=rs.getString(2);
                String job=rs.getString(3);
                int mgr=rs.getInt(4);
                String hireDate=rs.getString(5);
                Double sal=rs.getDouble(6);
                Double comm=rs.getDouble(7);
                int deptNo=rs.getInt(8);
                //将查询处理的一行数据存储到一个对象中
                Emp emp=new Emp(empNo1,ename,job,mgr,hireDate,sal,comm,deptNo,1);
                return emp;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBManager.closeConn(conn,ps,rs);
        }
        return null;
    }

    //7.分页查询
    public List<Emp> find(int start,int size){
        //产生一个集合
        List<Emp> list=new ArrayList<>();
        conn=DBManager.getConnection();
        String sql="select * from gaofeng2207.emp where empstate=1 limit ?,?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,11);
            ps.setInt(2,5);
             rs = ps.executeQuery();
             while (rs.next()){
                 int empNo=rs.getInt(1);
                 String ename=rs.getString(2);
                 String job=rs.getString(3);
                 int mgr=rs.getInt(4);
                 String hireDate=rs.getString(5);
                 Double sal=rs.getDouble(6);
                 Double comm=rs.getDouble(7);
                 int deptNo=rs.getInt(8);
//将查询到的数据封装到一个对象中
                 Emp emp=new Emp(empNo,ename,job,mgr,hireDate,sal,comm,deptNo,1);
                 //将产生的对象放到集合中
                 list.add(emp);
             }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBManager.closeConn(conn,ps,rs);
        }
        return list;
    }

    //8.模糊查询
    public List<Emp> find(String name){
        //产生一个集合
        List<Emp> list=new ArrayList<>();
        conn=DBManager.getConnection();
        String sql="select * from gaofeng2207.emp where empstate=1 and ename like ?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,"%"+name+"%");
            rs = ps.executeQuery();
            while (rs.next()){
                int empNo=rs.getInt(1);
                String ename=rs.getString(2);
                String job=rs.getString(3);
                int mgr=rs.getInt(4);
                String  hireDate=rs.getString(5);
                Double sal=rs.getDouble(6);
                Double comm=rs.getDouble(7);
                int deptNo=rs.getInt(8);
                //将接收到的数据封装为一个对象
                Emp emp=new Emp(empNo,ename,job,mgr,hireDate,sal,comm,deptNo,1);
                //将对象放到集合中
                list.add(emp);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBManager.closeConn(conn,ps,rs);
        }
        //返回集合
        return list;
    }























}
